<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>计算核算地区的调节服务占比</title>
    <script src="../js/xlsx.full.min.js"></script>
    <style>
        body {
            font-family: Arial, sans-serif;
            text-align: center;
            margin-top: 50px;
        }
        nav {
            margin-bottom: 20px;
        }
        nav a {
            margin: 0 10px;
            text-decoration: none;
            color: #007BFF;
            font-weight: bold;
        }
        nav a:hover {
            text-decoration: underline;
        }
    </style>
</head>
<body>
    <nav>
                <a href="../index.html">首页</a>
        <a href="calculate_service_percentage.html">计算调节服务占比</a>
        <a href="pie_service_percentage.html">核算地区调节服务占比可视化</a>
        <a href="single_service.html">单项调节各地区占比</a>
        <a href="adjust_service_value.html">调整气候调节服务项的价值量</a>
        <a href="multi_service.html">多项调节服务可视化</a>
    </nav>
    <h1>计算核算地区的调节服务占比</h1>
    <input type="file" id="fileInput" accept=".xlsx, .xls">
    <button id="calculateButton">计算并保存</button>
    <p id="statusMessage"></p>

    <script>
        document.getElementById('calculateButton').addEventListener('click', function() {
            const fileInput = document.getElementById('fileInput');
            const file = fileInput.files[0];
            if (!file) {
                document.getElementById('statusMessage').textContent = '请选择一个文件';
                return;
            }

            const reader = new FileReader();
            reader.onload = function(e) {
                const data = e.target.result;
                const workbook = XLSX.read(data, {type: 'binary'});
                const sheetName = workbook.SheetNames[0];
                const worksheet = workbook.Sheets[sheetName];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, {header: 1});

                // Extract headers
                const headers = jsonData[0];
                const regionIndex = headers.indexOf('核算地区');
                const serviceIndex = headers.indexOf('调节服务项');
                const valueIndex = headers.indexOf('价值量总值（元）');

                // Calculate the total value for each region and service
                const regionServiceValues = {};
                jsonData.slice(1).forEach(row => {
                    const region = row[regionIndex];
                    const service = row[serviceIndex];
                    const value = parseFloat(row[valueIndex]);
                    if (!isNaN(value)) {
                        if (!regionServiceValues[region]) {
                            regionServiceValues[region] = {};
                        }
                        regionServiceValues[region][service] = (regionServiceValues[region][service] || 0) + value;
                    }
                });

                // Calculate the total value for each region
                const regionTotalValues = {};
                for (const region in regionServiceValues) {
                    regionTotalValues[region] = Object.values(regionServiceValues[region]).reduce((a, b) => a + b, 0);
                }

                // Calculate the percentage for each service in each region
                jsonData.slice(1).forEach(row => {
                    const region = row[regionIndex];
                    const service = row[serviceIndex];
                    const value = parseFloat(row[valueIndex]);
                    if (!isNaN(value)) {
                        const totalValue = regionTotalValues[region];
                        const percentage = (value / totalValue) * 100;
                        row.push(percentage.toFixed(8));
                    }
                });

                // Add a new header for the percentage
                headers.push('调节服务项占比（%）');
                jsonData[0] = headers;

                // Convert the updated data back to a worksheet
                const newWorksheet = XLSX.utils.aoa_to_sheet(jsonData);
                const newWorkbook = XLSX.utils.book_new();
                XLSX.utils.book_append_sheet(newWorkbook, newWorksheet, sheetName);

                // Save the new workbook
                XLSX.writeFile(newWorkbook, 'calculated_service_percentage.xlsx');
                document.getElementById('statusMessage').textContent = '计算完成并保存为 calculated_service_percentage.xlsx';
            };
            reader.readAsBinaryString(file);
        });
    </script>
</body>
</html>